package persistence.factura.facturaDAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import persistence.DAO.DAOHelper;
import persistence.DAO.DataBaseException;
import business.article.entities.Article;
import business.sale.entities.ArticleBill;
import business.sale.entities.Factura;
import business.sale.entities.Sale;

public class FacturaDAO {

	/**
	 * Singleton implementation begin
	 */
	private static FacturaDAO instance = null;

	private FacturaDAO() {
	}

	public static FacturaDAO getInstance() {
		if (instance == null)
			instance = new FacturaDAO();
		return instance;
	}
	/**
	 * Singleton implementation end
	 * 
	 * @throws badClassException
	 */
	public void commitFactura(Factura fact){
		PreparedStatement ps=null;
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			String sql="INSERT INTO Bill " +
					"(inner_key,id_sale,id_payment,discount,totalDiscIncluded,date,total)" +
					" VALUES (" +
					String.valueOf(fact.getPrimaryKey())+"," +
					String.valueOf(fact.getVenta().getSaleKey())+"," +
					String.valueOf(0)+"," +
					String.valueOf(fact.getDescuento())+"," +
					String.valueOf(fact.getTotalConDesc())+",?," +
					String.valueOf(fact.getTotal()) +
					")";
			ps=con.prepareStatement(sql);
			ps.setDate(1, new Date(fact.getDate().getTime()));
			ps.execute();
			
			for (ArticleBill artb:fact.getArts()){
				addArticleBill(artb);
			}
			updateDate(fact.getVenta());
			updateClient(fact.getVenta());
		}  catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
	};
	
	private void addArticleBill(ArticleBill artb) throws SQLException {
	Connection con=DAOHelper.getInstance().getConnection();
	Statement st=con.createStatement();
		st.execute("INSERT INTO Article_bill" +
				"(id_bill,bill_line,id_article,total_price,unit_price,quant)" +
				"VALUES (" +
				String.valueOf(artb.getIdBill())+"," +
				String.valueOf(artb.getIdLine())+"," +
				String.valueOf(getArticleInnerKey(artb.getArt()))+"," +
				String.valueOf(artb.getTotalprice())+"," +
				String.valueOf(artb.getUnitPrice())+"," +
				String.valueOf(artb.getQuant())+
				")");
		con.close();
	}

	private int getArticleInnerKey(Article art) throws  SQLException {
		Connection con=DAOHelper.getInstance().getConnection();
		Statement st=con.createStatement();
		ResultSet s = st.executeQuery("SELECT inner_key FROM Article WHERE primary_key="+ String.valueOf(art.getPrimaryKey()));
		s.next();
		int inner_id_articulo = s.getInt(1);
		con.close();
		return inner_id_articulo;
	}

	public int  nextFreeIdBill() throws DataBaseException{
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r;
			r=st.executeQuery("SELECT MAX(inner_key) inner_key FROM Bill");
			if (r.next()){
			return r.getInt(1)+1;}
			return 0;
		} catch (SQLException e) {
			throw new DataBaseException(null);
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		
	}
	
	private void updateDate(Sale sale){

		String sql="UPDATE Sale SET ";
		sql=sql+"close_date= ? ";
		sql=sql+"WHERE inner_key="+String.valueOf(sale.getSaleKey());
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			PreparedStatement ps;
			ps=con.prepareStatement(sql);
			ps.setDate(1, new Date(sale.getCloseDate().getTime()));
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }

	};
	
	private void updateClient(Sale sale){

		String sql="UPDATE Sale SET ";
		sql=sql+"id_client="+String.valueOf(sale.getClient().getClientId())+" ";
		sql=sql+"WHERE inner_key="+String.valueOf(sale.getSaleKey());
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			
			con.createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }

	};
}
